﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using PMS.ENT;
using PMS.Utilities;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data.SqlClient;

namespace PMS.DAL
{
    public class UserDAL
    {
        #region Common
        private static UserENT CreateUserFromReader(IDataReader reader)
        {
            UserENT item = new UserENT();
            try
            {

                if (!reader.IsDBNull(reader.GetOrdinal(UserColumns.UserID.ToString()))) item.UserID = Utility.IntParse(reader[UserColumns.UserID.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(UserColumns.UserName.ToString()))) item.UserName = Utility.StringParse(reader[UserColumns.UserName.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(UserColumns.Password.ToString()))) item.Password = Utility.StringParse(reader[UserColumns.Password.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(UserColumns.FullName.ToString()))) item.FullName = Utility.StringParse(reader[UserColumns.FullName.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(UserColumns.DateOfBirth.ToString()))) item.DateOfBirth = Utility.DateTimeParse(reader[UserColumns.DateOfBirth.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(UserColumns.Sex.ToString()))) item.Sex = Utility.StringParse(reader[UserColumns.Sex.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(UserColumns.Email.ToString()))) item.Email = Utility.StringParse(reader[UserColumns.Email.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(UserColumns.ImagePath.ToString()))) item.ImagePath = Utility.StringParse(reader[UserColumns.ImagePath.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(UserColumns.DepartmentID.ToString()))) item.DepartmentID = Utility.IntParse(reader[UserColumns.DepartmentID.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(UserColumns.RoleID.ToString()))) item.RoleID = Utility.IntParse(reader[UserColumns.RoleID.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(UserColumns.CreatedBy.ToString()))) item.CreatedBy = Utility.IntParse(reader[UserColumns.CreatedBy.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(UserColumns.UpdateBy.ToString()))) item.UpdateBy = Utility.IntParse(reader[UserColumns.UpdateBy.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(UserColumns.CreatedDate.ToString()))) item.CreatedDate = Utility.DateTimeParse(reader[UserColumns.CreatedDate.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(UserColumns.UpdateDate.ToString()))) item.UpdateDate = Utility.DateTimeParse(reader[UserColumns.UpdateDate.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(UserColumns.Isdeleted.ToString()))) item.Isdeleted = Utility.IntParse(reader[UserColumns.Isdeleted.ToString()]);

                DataTable dt = reader.GetSchemaTable();
                if (Utility.FieldExist(dt, UserColumns.DepartmentName.ToString()))
                {
                    if (!reader.IsDBNull(reader.GetOrdinal(UserColumns.DepartmentName.ToString()))) item.DepartmentName = Utility.StringParse(reader[UserColumns.DepartmentName.ToString()]);

                }
                if (Utility.FieldExist(dt, UserColumns.Role.ToString()))
                {
                    if (!reader.IsDBNull(reader.GetOrdinal(UserColumns.Role.ToString()))) item.Role = Utility.StringParse(reader[UserColumns.Role.ToString()]);

                }

            }
            catch
            {
                throw;
            }
            return item;
        }
        #endregion

        #region Get
        public static UserENT GetUserByID(int userID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procUserGetByID.ToString());
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);

                UserENT user = new UserENT();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    if (reader.Read())
                    {
                        user = CreateUserFromReader(reader);
                    }
                    reader.Close();
                }

                return user;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetUserByID");
            }
        }

        public static UserCollection GetUserByIDAndGroupID(int userID,int groupID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procUserGetByIDAndGroupID.ToString());
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);
                database.AddInParameter(dbCommand, "@GroupID", DbType.Int32, groupID);
                UserCollection listUser = new UserCollection();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listUser.Add(CreateUserFromReader(reader));
                    }
                    reader.Close();
                }


                return listUser;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetUserByID");
            }
        }
        public static UserENT GetUserByUserCode(string userCode)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procUserGetByUserCode.ToString());
                database.AddInParameter(dbCommand, "@UserCode", DbType.String, userCode);

                UserENT user = new UserENT();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    if (reader.Read())
                    {
                        user = CreateUserFromReader(reader);
                    }
                    reader.Close();
                }

                return user;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetUserByCode");
            }
        }
        public static UserENT GetUserByEmail(string email)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procUserGetByEmail.ToString());
                database.AddInParameter(dbCommand, "@Email", DbType.String, email);

                UserENT user = new UserENT();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    if (reader.Read())
                    {
                        user = CreateUserFromReader(reader);
                    }
                    reader.Close();
                }

                return user;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetUserByEmail");
            }
        }

        public static UserENT GetUserByUserName(string userName)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procUserGetByUserName.ToString());
                database.AddInParameter(dbCommand, "@UserName", DbType.String, userName);

                UserENT user = new UserENT();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    if (reader.Read())
                    {
                        user = CreateUserFromReader(reader);
                    }
                    reader.Close();
                }

                return user;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetUserByUserName");
            }
        }

        public static UserCollection GetUserList(string fullName, int departmentID, int roleID, string orderBy, string orderDirection, int page, int pageSize, out int totalRecords)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procUserGetList.ToString());
                UserCollection listUser = new UserCollection();

                database.AddInParameter(dbCommand, "@FullName", DbType.String, fullName);
                database.AddInParameter(dbCommand, "@DepartmentID", DbType.Int32, departmentID);
                database.AddInParameter(dbCommand, "@RoleID", DbType.Int32, roleID);
                database.AddInParameter(dbCommand, "@OrderBy", DbType.String, orderBy);
                database.AddInParameter(dbCommand, "@OrderDirection", DbType.AnsiString, orderDirection);
                database.AddInParameter(dbCommand, "@Page", DbType.Int32, page);
                database.AddInParameter(dbCommand, "@PageSize", DbType.Int32, pageSize);
                database.AddOutParameter(dbCommand, "@TotalRecords", DbType.Int32, 0);

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listUser.Add(CreateUserFromReader(reader));
                    }
                    reader.Close();
                }

                totalRecords = (int)database.GetParameterValue(dbCommand, "@TotalRecords");

                return listUser;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetUserList");
            }
        }

        public static UserCollection GetUserListForCMB(int roleID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procUserGetListForCMB.ToString());
                database.AddInParameter(dbCommand, "@RoleID", DbType.Int32, roleID);
                UserCollection listUser = new UserCollection();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listUser.Add(CreateUserFromReader(reader));
                    }
                    reader.Close();
                }

                return listUser;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetUserListForCMB");
            }
        }

        public static UserCollection GetUserListByGroupID(int groupID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procUserGetListByGroupID.ToString());

                UserCollection listUser = new UserCollection();
                database.AddInParameter(dbCommand, "@GroupID", DbType.Int32, groupID);

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listUser.Add(CreateUserFromReader(reader));
                    }
                    reader.Close();
                }

                return listUser;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetUserListForCMB");
            }
        }
        #endregion

        #region Insert, Update, Delete

        public static int InsertUser(UserENT user)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procUserInsert.ToString());
                int userID = 0;
                database.AddOutParameter(dbCommand, "@UserID", DbType.Int32, user.UserID);
                database.AddInParameter(dbCommand, "@UserName", DbType.String, user.UserName);
                database.AddInParameter(dbCommand, "@Password", DbType.String, user.Password);
                database.AddInParameter(dbCommand, "@FullName", DbType.String, user.FullName);
                database.AddInParameter(dbCommand, "@DateOfBirth", DbType.DateTime, user.DateOfBirth);
                database.AddInParameter(dbCommand, "@Sex", DbType.String, user.Sex);
                database.AddInParameter(dbCommand, "@Email", DbType.String, user.Email);
                database.AddInParameter(dbCommand, "@ImagePath", DbType.String, user.ImagePath);
                database.AddInParameter(dbCommand, "@DepartmentID", DbType.Int32, user.DepartmentID);
                database.AddInParameter(dbCommand, "@RoleID", DbType.Int32, user.RoleID);
                database.AddInParameter(dbCommand, "@CreatedBy", DbType.Int32, user.CreatedBy);
                database.AddInParameter(dbCommand, "@UpdateBy", DbType.Int32, user.UpdateBy);
                database.AddInParameter(dbCommand, "@Isdeleted", DbType.Int32, user.Isdeleted);
                database.AddInParameter(dbCommand, "@UserCode", DbType.String, user.UserCode);
                if (database.ExecuteNonQuery(dbCommand) > 0)
                {
                    userID = Utility.IntParse(database.GetParameterValue(dbCommand, "@UserID"));
                    return userID;
                }
                return -1;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_InsertUser");
            }
        }

        public static int UpdateUser(UserENT user)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procUserUpdate.ToString());

                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, user.UserID);
                database.AddInParameter(dbCommand, "@UserName", DbType.String, user.UserName);
                database.AddInParameter(dbCommand, "@Password", DbType.String, user.Password);
                database.AddInParameter(dbCommand, "@FullName", DbType.String, user.FullName);
                //error occur
                //database.AddInParameter(dbCommand, "@DateOfBirth", DbType.DateTime, user.DateOfBirth);
                //set temp
                database.AddInParameter(dbCommand, "@DateOfBirth", DbType.DateTime, DateTime.Now);
                database.AddInParameter(dbCommand, "@Sex", DbType.String, user.Sex);
                database.AddInParameter(dbCommand, "@Email", DbType.String, user.Email);
                database.AddInParameter(dbCommand, "@ImagePath", DbType.String, user.ImagePath);
                database.AddInParameter(dbCommand, "@DepartmentID", DbType.Int32, user.DepartmentID);
                database.AddInParameter(dbCommand, "@RoleID", DbType.Int32, user.RoleID);
                database.AddInParameter(dbCommand, "@UpdateBy", DbType.Int32, user.UpdateBy);
                database.AddInParameter(dbCommand, "@Isdeleted", DbType.Int32, user.Isdeleted);

                if (database.ExecuteNonQuery(dbCommand) > 0)
                {
                    int userID = Utility.IntParse(database.GetParameterValue(dbCommand, "@UserID"));
                    return userID;
                }
                return -1;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_InsertUser");
            }
        }

        public static int DeleteUser(string strUserID, int userID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procUserDelete.ToString());
                database.AddInParameter(dbCommand, "@StrUserID", DbType.String, strUserID);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);

                if (database.ExecuteNonQuery(dbCommand) > 0)
                {
                    return 1;
                }
                return 0;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_DeleteUser");
            }
        }
        #endregion
    }
}
